package com.zheng.study.core.dao.impl;

import cn.hutool.core.util.StrUtil;
import com.zheng.study.base.dao.impl.BaseDaoImpl;
import com.zheng.study.core.dao.TmedtechFlowDao;
import com.zheng.study.core.entity.TmedtechFlow;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * newFile
 * ============================================================================
 * author : dzw
 * createDate:  2019/2/14 。
 * ============================================================================
 */
@Repository
public class TmedtechFlowDaoImpl extends BaseDaoImpl<TmedtechFlow, String> implements TmedtechFlowDao {
	@Override
	public List<Object> getList() {
		String sql = "select t.flow_sequence,t.medtech_seq_no,t.visit_id,t.nstation_id,t.bed_no,t.valid_flag,t.flow_flag,t.item_id,t.item_name" +
				",t.body_part,t.execute_dept,t.pat_status,t.emc_flag,t.execute_date,t.execute_time,t.remarks,t.flow_status,t.insert_by,t.insert_time" +
				",t.confirm_by,t.confirm_time,t.process_status,t.field1,t.field2,t.pat_name,t.pat_no,t.pat_label from tmedtechFlow t";
		return getSession().createSQLQuery(sql).list();
	}

	@Override
	public List<Object> getNewList(String startDate, String endDate, String hasSeqNo) {
		if(StrUtil.isBlank(hasSeqNo)){
			hasSeqNo = "";
		}
		String sql = "select\n" +
				"[FLOW_SEQUENCE] = stuff((SELECT ';' + [FLOW_SEQUENCE] FROM TMEDTECHFLOW t WHERE t.MEDTECH_SEQ_NO=t2.MEDTECH_SEQ_NO FOR xml path('')),1,1,''),\n" +
				"MEDTECH_SEQ_NO,\n" +
				"[ITEM_NAME] = stuff((SELECT ';' + [ITEM_NAME] FROM TMEDTECHFLOW t WHERE t.MEDTECH_SEQ_NO=t2.MEDTECH_SEQ_NO FOR xml path('')),1,1,''),\n" +
				"max(EXECUTE_DATE) as EXECUTE_DATE,\n" +
				"max(EXECUTE_TIME) as EXECUTE_TIME,\n" +
				"max(PAT_STATUS) as PAT_STATUS,\n" +
				"max(PAT_NAME) as PAT_NAME,\n" +
				"max(BED_NO) as BED_NO,\n" +
				"max(PAT_LABEL) as PAT_LABEL,\n" +
				"[REMARKS] = stuff((SELECT ';' + [REMARKS] FROM TMEDTECHFLOW t WHERE t.MEDTECH_SEQ_NO=t2.MEDTECH_SEQ_NO FOR xml path('')),1,1,''),\n" +
				"max(EXECUTE_DEPT) as EXECUTE_DEPT,\n" +
				"max(VISIT_ID) as VISIT_ID,\n" +
				"max(EMC_FLAG) as EMC_FLAG\n" +
				"FROM TMEDTECHFLOW t2 \n" +
				"WHERE " +
				"CONVERT(VARCHAR(10),EXECUTE_DATE,120)>='"+startDate+"'\n" +
				"AND CONVERT(varchar(10),EXECUTE_DATE,120)<'"+endDate+"'\n"+
				"AND EXECUTE_DEPT in ('9909151500','9924000000','9937020001')\n"+
				"AND charindex(FLOW_SEQUENCE,'"+hasSeqNo+"')<=0\n" +
				"AND BED_NO <> ''\n"+
				"GROUP BY MEDTECH_SEQ_NO";
//		return getSession().createSQLQuery(sql).list();

		List<Object> objectList = new ArrayList<Object>();
		Connection conn = getConn();
		PreparedStatement pstmt;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			int col = rs.getMetaData().getColumnCount();
			while (rs.next()){
				Object[] oArr = new Object[13];
				for(int i=1;i<=col;i++){
					String s = rs.getString(i);
					if(i==3){
						String[] nameArr = s.split(";");
						String nameRs = nameArr.length>0 ? nameArr[0]+"," : "";
						for(String name : nameArr){
							if(!StrUtil.equals(name+",", nameRs)){
								nameRs += name + ",";
							}
						}
						oArr[i-1] = StrUtil.isNotBlank(nameRs) ? nameRs.substring(0, nameRs.length()-1) : "";
					}else{
						oArr[i-1] = StrUtil.isNotBlank(s) ? s.trim() : "";
					}
				}
				objectList.add(oArr);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return objectList;
	}

	@Override
	public Object getObjBySequence(String flow_sequence) {
		String sql = "select t.flow_sequence,t.medtech_seq_no,t.visit_id,t.nstation_id,t.bed_no,t.valid_flag,t.flow_flag,t.item_id,t.item_name" +
				",t.body_part,t.execute_dept,t.pat_status,t.emc_flag,t.execute_date,t.execute_time,t.remarks,t.flow_status,t.insert_by,t.insert_time" +
				",t.confirm_by,t.confirm_time,t.process_status,t.field1,t.field2,t.pat_name,t.pat_no,t.pat_label from tmedtechFlow t" +
				" where t.flow_sequence = '"+flow_sequence+"'";
		List<Object> list = getSession().createSQLQuery(sql).list();
		return list.size()>0 ? list.get(0) : null;
	}

	@Override
	public String getProcessStatusBySequence(String flow_sequence) {
		String sql = "select t.process_status from tmedtechFlow t  where t.flow_sequence = '"+flow_sequence+"'";
		Object status = getSession().createSQLQuery(sql).uniqueResult();
		return status!=null ? String.valueOf(status) : null;
	}

	@Override
	public int updateProcessStatus(String flow_sequence, String status) {
		String sql = "UPDATE TMEDTECHFLOW set PROCESS_STATUS='"+status+"',UPDATE_DATE=getdate(),UPDATE_TIME=getdate() where MEDTECH_SEQ_NO='"+flow_sequence+"'";
		return getSession().createSQLQuery(sql).executeUpdate();
	}

	private static Connection getConn(){
		String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
//		String url = "jdbc:sqlserver://127.0.0.1:1433;databaseName=MED_TEMP";
		String url = "jdbc:sqlserver://192.168.110.111:1433;databaseName=MED_TEMP";
		String username = "medtemp";
		String password = "medtemp";
		Connection conn = null;
		try {
			Class.forName(driver);
			conn = (Connection) DriverManager.getConnection(url, username, password);
		} catch (ClassNotFoundException e){
			e.printStackTrace();
		} catch (SQLException e){
			e.printStackTrace();
		}
		return conn;
	}
}
